<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>hive的SQL使用 | flybird&#39;s blog</title>
    <meta name="generator" content="VuePress 1.8.0">
    <link rel="icon" href="https://infinitypro-img.infinitynewtab.com/custom-icon/8001de1jd3n68lbfnxxt564xvb0vl5.png?imageMogr2/thumbnail/240x/format/webp/blur/1x0/quality/100|imageslim">
    <script language="javascript" type="text/javascript" src="https://cdn.staticfile.org/jquery/1.7.2/jquery.min.js"></script>
    <script language="javascript" type="text/javascript" src="/js/MouseClickEffect.js"></script>
    <meta name="description" content="Stay hungry, Stay foolish">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    
    <link rel="preload" href="/assets/css/0.styles.324c77a5.css" as="style"><link rel="preload" href="/assets/js/app.55c4f7c3.js" as="script"><link rel="preload" href="/assets/js/5.86109dd2.js" as="script"><link rel="preload" href="/assets/js/1.74974061.js" as="script"><link rel="preload" href="/assets/js/2.ab8aa845.js" as="script"><link rel="preload" href="/assets/js/53.861fb703.js" as="script"><link rel="preload" href="/assets/js/18.cfe435f1.js" as="script"><link rel="prefetch" href="/assets/js/10.1f3187f9.js"><link rel="prefetch" href="/assets/js/11.67fe3d1f.js"><link rel="prefetch" href="/assets/js/12.0052f325.js"><link rel="prefetch" href="/assets/js/13.bad60b36.js"><link rel="prefetch" href="/assets/js/14.6a893378.js"><link rel="prefetch" href="/assets/js/15.899648d3.js"><link rel="prefetch" href="/assets/js/16.0575f88b.js"><link rel="prefetch" href="/assets/js/17.86f820d7.js"><link rel="prefetch" href="/assets/js/19.1d1c7b67.js"><link rel="prefetch" href="/assets/js/20.218819f6.js"><link rel="prefetch" href="/assets/js/21.223949af.js"><link rel="prefetch" href="/assets/js/22.eb6ea80c.js"><link rel="prefetch" href="/assets/js/23.cf07ba0c.js"><link rel="prefetch" href="/assets/js/24.eeb9d511.js"><link rel="prefetch" href="/assets/js/25.4be64ae7.js"><link rel="prefetch" href="/assets/js/26.6aea0ced.js"><link rel="prefetch" href="/assets/js/27.7540f161.js"><link rel="prefetch" href="/assets/js/28.d88a26e6.js"><link rel="prefetch" href="/assets/js/29.7edc404c.js"><link rel="prefetch" href="/assets/js/30.b3c16bef.js"><link rel="prefetch" href="/assets/js/31.63392d40.js"><link rel="prefetch" href="/assets/js/32.b90695c8.js"><link rel="prefetch" href="/assets/js/33.669186a5.js"><link rel="prefetch" href="/assets/js/34.57a9b7fc.js"><link rel="prefetch" href="/assets/js/35.81d5b8b3.js"><link rel="prefetch" href="/assets/js/36.466fde53.js"><link rel="prefetch" href="/assets/js/37.ff7b9321.js"><link rel="prefetch" href="/assets/js/38.132de18a.js"><link rel="prefetch" href="/assets/js/39.80a51963.js"><link rel="prefetch" href="/assets/js/40.e7d6c67d.js"><link rel="prefetch" href="/assets/js/41.d195ea4b.js"><link rel="prefetch" href="/assets/js/42.dbe1a8f1.js"><link rel="prefetch" href="/assets/js/43.2ea02aec.js"><link rel="prefetch" href="/assets/js/44.294015bc.js"><link rel="prefetch" href="/assets/js/45.3cf9c326.js"><link rel="prefetch" href="/assets/js/46.8cc95701.js"><link rel="prefetch" href="/assets/js/47.ddf6cc44.js"><link rel="prefetch" href="/assets/js/48.ba718ca6.js"><link rel="prefetch" href="/assets/js/49.feee5279.js"><link rel="prefetch" href="/assets/js/50.6f2d0d0d.js"><link rel="prefetch" href="/assets/js/51.ebe1eb07.js"><link rel="prefetch" href="/assets/js/52.3e47f7ae.js"><link rel="prefetch" href="/assets/js/54.f7d3a937.js"><link rel="prefetch" href="/assets/js/55.d90baa98.js"><link rel="prefetch" href="/assets/js/56.ba7f3256.js"><link rel="prefetch" href="/assets/js/57.90441d7f.js"><link rel="prefetch" href="/assets/js/58.a0d58abc.js"><link rel="prefetch" href="/assets/js/59.7f649313.js"><link rel="prefetch" href="/assets/js/6.00dfc51e.js"><link rel="prefetch" href="/assets/js/60.60dd83d2.js"><link rel="prefetch" href="/assets/js/61.b84cd1ef.js"><link rel="prefetch" href="/assets/js/62.8ed6541f.js"><link rel="prefetch" href="/assets/js/63.84bfbbbf.js"><link rel="prefetch" href="/assets/js/64.01c483db.js"><link rel="prefetch" href="/assets/js/65.278ce63e.js"><link rel="prefetch" href="/assets/js/66.cb493053.js"><link rel="prefetch" href="/assets/js/67.8c2201b6.js"><link rel="prefetch" href="/assets/js/68.46467c87.js"><link rel="prefetch" href="/assets/js/69.ee34297d.js"><link rel="prefetch" href="/assets/js/7.aaee1baa.js"><link rel="prefetch" href="/assets/js/70.3a016bcf.js"><link rel="prefetch" href="/assets/js/71.1cb787d0.js"><link rel="prefetch" href="/assets/js/72.2ee0677e.js"><link rel="prefetch" href="/assets/js/73.3e1e1729.js"><link rel="prefetch" href="/assets/js/74.947d7cd3.js"><link rel="prefetch" href="/assets/js/75.2e5c7344.js"><link rel="prefetch" href="/assets/js/76.6c098cc2.js"><link rel="prefetch" href="/assets/js/77.03fd3a89.js"><link rel="prefetch" href="/assets/js/78.38d2d963.js"><link rel="prefetch" href="/assets/js/79.ea508df9.js"><link rel="prefetch" href="/assets/js/8.35d0b089.js"><link rel="prefetch" href="/assets/js/80.b2b1dda3.js"><link rel="prefetch" href="/assets/js/81.d7db5a4d.js"><link rel="prefetch" href="/assets/js/82.1c148069.js"><link rel="prefetch" href="/assets/js/83.51e2c0d0.js"><link rel="prefetch" href="/assets/js/84.6118b266.js"><link rel="prefetch" href="/assets/js/85.55a516f8.js"><link rel="prefetch" href="/assets/js/86.3b73f7a9.js"><link rel="prefetch" href="/assets/js/87.123149cb.js"><link rel="prefetch" href="/assets/js/88.f87dde9d.js"><link rel="prefetch" href="/assets/js/89.06c3b9a1.js"><link rel="prefetch" href="/assets/js/9.58e6c220.js"><link rel="prefetch" href="/assets/js/vendors~flowchart.080f3458.js">
    <link rel="stylesheet" href="/assets/css/0.styles.324c77a5.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div><div class="theme-container" data-v-0b87bb44><div data-v-0b87bb44><div id="loader-wrapper" class="loading-wrapper" data-v-1c4f0192 data-v-0b87bb44 data-v-0b87bb44><div class="loader-main" data-v-1c4f0192><div data-v-1c4f0192></div><div data-v-1c4f0192></div><div data-v-1c4f0192></div><div data-v-1c4f0192></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-73c95a87 data-v-0b87bb44 data-v-0b87bb44><h3 class="title" style="display:none;" data-v-73c95a87 data-v-73c95a87>flybird's blog</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-73c95a87 data-v-73c95a87><input type="password" value="" data-v-73c95a87> <span data-v-73c95a87>Konck! Knock!</span> <button data-v-73c95a87>OK</button></label> <div class="footer" style="display:none;" data-v-73c95a87 data-v-73c95a87><span data-v-73c95a87><i class="iconfont reco-theme" data-v-73c95a87></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-73c95a87>vuePress-theme-reco</a></span> <span data-v-73c95a87><i class="iconfont reco-copyright" data-v-73c95a87></i> <a data-v-73c95a87><span data-v-73c95a87>flybird</span>
            
          <span data-v-73c95a87>2020 - </span>
          2021
        </a></span></div></div> <div class="hide" data-v-0b87bb44><header class="navbar" data-v-0b87bb44><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><!----> <span class="site-name">flybird's blog</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/index.html" class="nav-link"><i class="iconfont reco-home"></i>
  主页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/大数据/" class="nav-link"><i class="iconfont undefined"></i>
  大数据
</a></li><li class="dropdown-item"><!----> <a href="/categories/后端/" class="nav-link"><i class="iconfont undefined"></i>
  后端
</a></li><li class="dropdown-item"><!----> <a href="/categories/中间件/" class="nav-link"><i class="iconfont undefined"></i>
  中间件
</a></li></ul></div></div><div class="nav-item"><a href="/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  标签
</a></div><div class="nav-item"><a href="/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  时间线
</a></div><div class="nav-item"><a href="/about/" class="nav-link"><i class="iconfont reco-message"></i>
  关于我
</a></div> <a href="https://github.com/f1ybird" target="_blank" rel="noopener noreferrer" class="repo-link"><i class="iconfont reco-github"></i>
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask" data-v-0b87bb44></div> <aside class="sidebar" data-v-0b87bb44><div class="personal-info-wrapper" data-v-7e653f02><img src="/vuepress/my-head.png" alt="author-avatar" class="personal-img" data-v-7e653f02> <h3 class="name" data-v-7e653f02>
    flybird
  </h3> <div class="num" data-v-7e653f02><div data-v-7e653f02><h3 data-v-7e653f02>63</h3> <h6 data-v-7e653f02>文章</h6></div> <div data-v-7e653f02><h3 data-v-7e653f02>26</h3> <h6 data-v-7e653f02>标签</h6></div></div> <hr data-v-7e653f02></div> <nav class="nav-links"><div class="nav-item"><a href="/index.html" class="nav-link"><i class="iconfont reco-home"></i>
  主页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/大数据/" class="nav-link"><i class="iconfont undefined"></i>
  大数据
</a></li><li class="dropdown-item"><!----> <a href="/categories/后端/" class="nav-link"><i class="iconfont undefined"></i>
  后端
</a></li><li class="dropdown-item"><!----> <a href="/categories/中间件/" class="nav-link"><i class="iconfont undefined"></i>
  中间件
</a></li></ul></div></div><div class="nav-item"><a href="/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  标签
</a></div><div class="nav-item"><a href="/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  时间线
</a></div><div class="nav-item"><a href="/about/" class="nav-link"><i class="iconfont reco-message"></i>
  关于我
</a></div> <a href="https://github.com/f1ybird" target="_blank" rel="noopener noreferrer" class="repo-link"><i class="iconfont reco-github"></i>
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><a href="/views/" aria-current="page" class="sidebar-link">概览</a></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>大数据</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>中间件</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>后端</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-73c95a87 data-v-0b87bb44><h3 class="title" style="display:none;" data-v-73c95a87 data-v-73c95a87>hive的SQL使用</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-73c95a87 data-v-73c95a87><input type="password" value="" data-v-73c95a87> <span data-v-73c95a87>Konck! Knock!</span> <button data-v-73c95a87>OK</button></label> <div class="footer" style="display:none;" data-v-73c95a87 data-v-73c95a87><span data-v-73c95a87><i class="iconfont reco-theme" data-v-73c95a87></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-73c95a87>vuePress-theme-reco</a></span> <span data-v-73c95a87><i class="iconfont reco-copyright" data-v-73c95a87></i> <a data-v-73c95a87><span data-v-73c95a87>flybird</span>
            
          <span data-v-73c95a87>2020 - </span>
          2021
        </a></span></div></div> <div data-v-0b87bb44><main class="page"><!----> <div class="page-title" style="display:none;"><h1>hive的SQL使用</h1> <hr> <div data-v-7b2e794a><i class="iconfont reco-account" data-v-7b2e794a><span data-v-7b2e794a>flybird</span></i> <i class="iconfont reco-date" data-v-7b2e794a><span data-v-7b2e794a>2021-02-14</span></i> <i class="iconfont reco-eye" data-v-7b2e794a><span id="/views/bigdata/hive%E7%9A%84SQL%E4%BD%BF%E7%94%A8.html" data-flag-title="Your Article Title" class="leancloud-visitors" data-v-7b2e794a><a class="leancloud-visitors-count" style="font-size:.9rem;font-weight:normal;color:#999;"></a></span></i> <i class="iconfont reco-tag tags" data-v-7b2e794a><span class="tag-item" data-v-7b2e794a>
      hive
    </span></i></div></div> <div class="theme-reco-content content__default" style="display:none;"><h1 id="hive-ddl"><a href="#hive-ddl" class="header-anchor">#</a> Hive DDL</h1> <p>wiki地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Overview</p> <div class="language- line-numbers-mode"><pre class="language-text"><code>CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  -- (Note: TEMPORARY available in Hive ``0.14``.``0` `and later)
 ``[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
 ``[COMMENT table_comment]
 ``[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
 ``[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
 ``[SKEWED BY (col_name, col_name, ...)         -- (Note: Available in Hive ``0.10``.``0` `and later)]
   ``ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
   ``[STORED AS DIRECTORIES]
 ``[
  ``[ROW FORMAT row_format] 
  ``[STORED AS file_format]
   ``| STORED BY ``'storage.handler.class.name'` `[WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive ``0.6``.``0` `and later)
 ``]
 ``[LOCATION hdfs_path]
 ``[TBLPROPERTIES (property_name=property_value, ...)]  -- (Note: Available in Hive ``0.6``.``0` `and later)
 ``[AS select_statement];  -- (Note: Available in Hive ``0.5``.``0` `and later; not supported ``for` `external tables)
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
 ``LIKE existing_table_or_view_name
 ``[LOCATION hdfs_path];
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>data_type
 ``: primitive_type
 ``| array_type
 ``| map_type
 ``| struct_type
 ``| union_type -- (Note: Available in Hive ``0.7``.``0` `and later)
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>primitive_type
 ``: TINYINT
 ``| SMALLINT
 ``| INT
 ``| BIGINT
 ``| BOOLEAN
 ``| FLOAT
 ``| DOUBLE
 ``| DOUBLE PRECISION -- (Note: Available in Hive ``2.2``.``0` `and later)
 ``| STRING
 ``| BINARY   -- (Note: Available in Hive ``0.8``.``0` `and later)
 ``| TIMESTAMP  -- (Note: Available in Hive ``0.8``.``0` `and later)
 ``| DECIMAL   -- (Note: Available in Hive ``0.11``.``0` `and later)
 ``| DECIMAL(precision, scale) -- (Note: Available in Hive ``0.13``.``0` `and later)
 ``| DATE    -- (Note: Available in Hive ``0.12``.``0` `and later)
 ``| VARCHAR   -- (Note: Available in Hive ``0.12``.``0` `and later)
 ``| CHAR    -- (Note: Available in Hive ``0.13``.``0` `and later)
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>array_type
 ``: ARRAY &lt; data_type &gt;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>map_type
 ``: MAP &lt; primitive_type, data_type &gt;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>struct_type
 ``: STRUCT &lt; col_name : data_type [COMMENT col_comment], ...&gt;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>union_type
  ``: UNIONTYPE &lt; data_type, data_type, ... &gt; -- (Note: Available in Hive ``0.7``.``0` `and later)
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>row_format
 ``: DELIMITED [FIELDS TERMINATED BY ``char` `[ESCAPED BY ``char``]] [COLLECTION ITEMS TERMINATED BY ``char``]
    ``[MAP KEYS TERMINATED BY ``char``] [LINES TERMINATED BY ``char``]
    ``[NULL DEFINED AS ``char``]  -- (Note: Available in Hive ``0.13` `and later)
 ``| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>file_format:
 ``: SEQUENCEFILE
 ``| TEXTFILE  -- (Default, depending on hive.``default``.fileformat configuration)
 ``| RCFILE   -- (Note: Available in Hive ``0.6``.``0` `and later)
 ``| ORC     -- (Note: Available in Hive ``0.11``.``0` `and later)
 ``| PARQUET   -- (Note: Available in Hive ``0.13``.``0` `and later)
 ``| AVRO    -- (Note: Available in Hive ``0.14``.``0` `and later)
 ``| JSONFILE  -- (Note: Available in Hive ``4.0``.``0` `and later)
 ``| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>column_constraint_specification:
 ``: [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>default_value:
 ``: [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language- line-numbers-mode"><pre class="language-text"><code>constraint_specification:
 ``: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
  ``[, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
  ``[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
  ``[, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
  ``[, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>练习：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 创建测试数据库
create database test;
use test;

-- 查看表的详细结构，包括schema,Partition等
desc formatted psn5;

-- 创建普通hive表（不包含行定义格式）
create table psn
(
id int,
name string,
likes array&lt;string&gt;,
address map&lt;string,string&gt;
)

-- 创建自定义行格式的hive表
create table psn2
(
id int,
name string,
likes array&lt;string&gt;,
address map&lt;string,string&gt;
)
row format delimited 
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'

-- 创建默认分隔符的hive表（^A、^B、^C）
	create table psn3
	(
	id int,
	name string,
	likes array&lt;string&gt;,
	address map&lt;string,string&gt;
	)
	row format delimited
	fields terminated by '\\001'
	collection items terminated by '\\002'
	map keys terminated by '\\003';
	--或者
	create table psn3
	(
	id int,
	name string,
	likes array&lt;string&gt;,
	address map&lt;string,string&gt;
	)
	
	
-- 创建hive的外部表(需要添加external和location的关键字)
create external table psn4
(
id int,
name string,
likes array&lt;string&gt;,
address map&lt;string,string&gt;
)
row format delimited 
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/study'

在之前创建的表都属于hive的内部表（psn,psn2,psn3）,而psn4属于hive的外部表，
	内部表跟外部表的区别：
		1、hive内部表创建的时候数据存储在hive的默认存储目录中，外部表在创建的时候需要制定额外的目录
		2、hive内部表删除的时候，会将元数据和数据都删除，而外部表只会删除元数据，不会删除数据
	应用场景:
		内部表:需要先创建表，然后向表中添加数据，适合做中间表的存储
		外部表：可以先创建表，再添加数据，也可以先有数据，再创建表，本质上是将hdfs的某一个目录的数据跟hive的表关联映射起来，因此适合原始数据的存储，不会因为误操作将数据给删除掉。

hive的分区表:
		hive默认将表的数据保存在某一个hdfs的存储目录下，当需要检索符合条件的某一部分数据的时候，需要全量遍历数据，io量比较大，效率比较低，因此可以采用分而治之的思想，将符合某些条件的数据放置在某一个目录，此时检索的时候只需要搜索指定目录即可，不需要全量遍历数据。（按照年、城市等）
	
-- 创建单分区表
create table psn5
(
id int,
name string,
likes array&lt;string&gt;,
address map&lt;string,string&gt;
)
partitioned by(gender string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'

-- 创建多分区表
create table psn6
(
id int,
name string,
likes array&lt;string&gt;,
address map&lt;string,string&gt;
)
partitioned by(gender string,age int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'

注意：
		1、当创建完分区表之后，在保存数据的时候，会在hdfs目录中看到分区列会成为一个目录，以多级目录的形式存在。
		2、当创建多分区表之后，插入数据的时候不可以只添加一个分区列，需要将所有的分区列都添加值。
		3、多分区表在添加分区列的值得时候，与顺序无关，与分区表的分区列的名称相关，按照名称就行匹配。
		
-- 给分区表添加分区列的值
	alter table table_name add partition(col_name=col_value)
-- 删除分区列的值
	alter table table_name drop partition(col_name=col_value)
	
注意:
		1、添加分区列的值的时候，如果定义的是多分区表，那么必须给所有的分区列都赋值。
		2、删除分区列的值的时候，无论是单分区表还是多分区表，都可以将指定的分区进行删除。

修复分区:
		在使用hive外部表的时候，可以先将数据上传到hdfs的某一个目录中，然后再创建外部表建立映射关系，如果在上传数据的时候，参考分区表的形式也创建了多级目录，那么此时创建完表之后，是查询不到数据的，原因是分区的元数据没有保存在mysql中，因此需要修复分区，将元数据同步更新到mysql中，此时才可以查询到元数据。具体操作如下：
		
	-- 在hdfs创建目录并上传文件
	hdfs dfs -mkdir /testp
	hdfs dfs -mkdir /testp/age=10
	hdfs dfs -mkdir /testp/age=20
	hdfs dfs -put /root/data/data /testp/age=10
	hdfs dfs -put /root/data/data /testp/age=20
	
  -- 创建外部表
	create external table psn7
	(
	id int,
	name string,
	likes array&lt;string&gt;,
	address map&lt;string,string&gt;
	)
	partitioned by(age int)
	row format delimited
	fields terminated by ','
	collection items terminated by '-'
	map keys terminated by ':'
	location '/testp'
	
	-- 查询结果（没有数据）
	select * from psn7;
  -- 修复分区
	msck repair table psn7;
  -- 查询结果（有数据）
	select * from psn7;
	
	存在的问题：
		以上面的方式创建hive的分区表会存在问题，每次插入的数据都是人为指定分区列的值，我们更加希望能够根据记录中的某一个字段来判断将数据插入到哪一个分区目录下，此时利用我们上面的分区方式是无法完成操作的，需要使用动态分区来完成相关操作。
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br><span class="line-number">74</span><br><span class="line-number">75</span><br><span class="line-number">76</span><br><span class="line-number">77</span><br><span class="line-number">78</span><br><span class="line-number">79</span><br><span class="line-number">80</span><br><span class="line-number">81</span><br><span class="line-number">82</span><br><span class="line-number">83</span><br><span class="line-number">84</span><br><span class="line-number">85</span><br><span class="line-number">86</span><br><span class="line-number">87</span><br><span class="line-number">88</span><br><span class="line-number">89</span><br><span class="line-number">90</span><br><span class="line-number">91</span><br><span class="line-number">92</span><br><span class="line-number">93</span><br><span class="line-number">94</span><br><span class="line-number">95</span><br><span class="line-number">96</span><br><span class="line-number">97</span><br><span class="line-number">98</span><br><span class="line-number">99</span><br><span class="line-number">100</span><br><span class="line-number">101</span><br><span class="line-number">102</span><br><span class="line-number">103</span><br><span class="line-number">104</span><br><span class="line-number">105</span><br><span class="line-number">106</span><br><span class="line-number">107</span><br><span class="line-number">108</span><br><span class="line-number">109</span><br><span class="line-number">110</span><br><span class="line-number">111</span><br><span class="line-number">112</span><br><span class="line-number">113</span><br><span class="line-number">114</span><br><span class="line-number">115</span><br><span class="line-number">116</span><br><span class="line-number">117</span><br><span class="line-number">118</span><br><span class="line-number">119</span><br><span class="line-number">120</span><br><span class="line-number">121</span><br><span class="line-number">122</span><br><span class="line-number">123</span><br><span class="line-number">124</span><br><span class="line-number">125</span><br><span class="line-number">126</span><br><span class="line-number">127</span><br><span class="line-number">128</span><br><span class="line-number">129</span><br><span class="line-number">130</span><br><span class="line-number">131</span><br><span class="line-number">132</span><br><span class="line-number">133</span><br><span class="line-number">134</span><br><span class="line-number">135</span><br><span class="line-number">136</span><br><span class="line-number">137</span><br><span class="line-number">138</span><br><span class="line-number">139</span><br><span class="line-number">140</span><br><span class="line-number">141</span><br><span class="line-number">142</span><br><span class="line-number">143</span><br><span class="line-number">144</span><br><span class="line-number">145</span><br><span class="line-number">146</span><br><span class="line-number">147</span><br><span class="line-number">148</span><br><span class="line-number">149</span><br><span class="line-number">150</span><br><span class="line-number">151</span><br><span class="line-number">152</span><br></div></div><h1 id="hive-dml"><a href="#hive-dml" class="header-anchor">#</a> Hive DML</h1> <p>wiki地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML</p> <p><strong>Loading files into tables</strong></p> <p>Hive does not do any transformation while loading data into tables. Load operations are currently pure <em><strong>copy/move</strong></em> operations that move datafiles into locations corresponding to Hive tables.</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
 
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

示例：

-- 加载本地数据到hive表
	load data local inpath '/root/data/data' into table psn;--(/root/data/data指的是本地linux目录)
-- 加载hdfs数据文件到hive表
	load data inpath '/data/data' into table psn;--(/data/data指的是hdfs的目录，没有local)
	
注意：
		1、load操作不会对数据做任何的转换修改操作。
		2、从本地linux load数据文件是复制文件的过程。
		3、从hdfs load数据文件是移动文件的过程。
		4、load操作也支持向分区表中load数据，只不过需要添加分区列的值。
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br></div></div><p><strong>Inserting data into Hive Tables from queries</strong></p> <p>Query Results can be inserted into tables by using the insert clause.</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>语法：

Standard syntax:
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
 
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;
FROM from_statement
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;
 
Hive extension (dynamic partition inserts):
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

示例：
-- 注意：这种方式插入数据的时候需要预先创建好结果表
-- 从表中查询数据插入结果表（标准语法）
	INSERT OVERWRITE TABLE psn9 SELECT id,name FROM psn
-- 从表中获取部分列插入到新表中（多个插入）
	from psn
	insert overwrite table psn9
	select id,name 
	insert into table psn10
	select id
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br></div></div><p><strong>Writing data into the filesystem from queries</strong></p> <p>Query results can be inserted into filesystem directories by using a slight variation of the syntax above:</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>语法：
Standard syntax:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...
 
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
 
  
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char] (Note: Only available starting with Hive 0.13)
        
示例：

-- 注意：路径千万不要填写根目录，会把所有的数据文件都覆盖
-- 将查询到的结果导入到hdfs文件系统中
	insert overwrite directory '/result' select * from psn;
-- 将查询的结果导入到本地文件系统中
	insert overwrite local directory '/result' select * from psn;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br></div></div><p><strong>Inserting values into tables from SQL</strong></p> <p>The INSERT...VALUES statement can be used to insert data into tables directly from SQL.</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>语法：（使用传统关系型数据库的方式插入数据，效率较低）
Standard Syntax:
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]
  
Where values_row is:
( value [, value ...] )
where a value is either null or any valid SQL literal

示例：
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2))
  CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC;
 
INSERT INTO TABLE students
  VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
 
 
CREATE TABLE pageviews (userid VARCHAR(64), link STRING, came_from STRING)
  PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC;
 
INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23')
  VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null);
 
INSERT INTO TABLE pageviews PARTITION (datestamp)
  VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
  
INSERT INTO TABLE pageviews
  VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br></div></div><p><strong>数据更新和删除</strong></p> <p>在官网中我们明确看到hive中是支持Update和Delete操作的，但是实际上，是需要事务的支持的，Hive对于事务的支持有很多的限制，如下图所示：</p> <p><img src="https://cdn.jsdelivr.net/gh/f1ybird/cloudimg/img/update&amp;delete.png" alt=""></p> <p>因此，在使用hive的过程中，我们一般不会产生删除和更新的操作，如果你需要测试的话，参考下面如下配置，在hive的hive-site.xml中添加如下配置：</p> <div class="language-xml line-numbers-mode"><pre class="language-xml"><code><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>property</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>name</span><span class="token punctuation">&gt;</span></span>hive.support.concurrency<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>name</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>value</span><span class="token punctuation">&gt;</span></span>true<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>value</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>property</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>property</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>name</span><span class="token punctuation">&gt;</span></span>hive.enforce.bucketing<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>name</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>value</span><span class="token punctuation">&gt;</span></span>true<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>value</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>property</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>property</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>name</span><span class="token punctuation">&gt;</span></span>hive.exec.dynamic.partition.mode<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>name</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>value</span><span class="token punctuation">&gt;</span></span>nonstrict<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>value</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>property</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>property</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>name</span><span class="token punctuation">&gt;</span></span>hive.txn.manager<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>name</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>value</span><span class="token punctuation">&gt;</span></span>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>value</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>property</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>property</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>name</span><span class="token punctuation">&gt;</span></span>hive.compactor.initiator.on<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>name</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>value</span><span class="token punctuation">&gt;</span></span>true<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>value</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>property</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>property</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>name</span><span class="token punctuation">&gt;</span></span>hive.compactor.worker.threads<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>name</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>value</span><span class="token punctuation">&gt;</span></span>1<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>value</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>property</span><span class="token punctuation">&gt;</span></span>

   // 操作语句
   create table test_trancaction (user_id Int,name String) clustered by (user_id) into 3 buckets stored as orc TBLPROPERTIES ('transactional'='true');
   create table test_insert_test(id int,name string) row format delimited fields TERMINATED BY ',';
   insert into test_trancaction select * from test_insert_test;
   update test_trancaction set name='jerrick_up' where id=1;
  
  // 数据文件
   1,jerrick
   2,tom
   3,jerry
   4,lily
   5,hanmei
   6,limlei
   7,lucky
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br></div></div></div> <footer class="page-edit" style="display:none;"><div class="edit-link"><a href="https://github.com/f1ybird/edit/master/docs/views/bigdata/hive的SQL使用.md" target="_blank" rel="noopener noreferrer">在 GitHub 上编辑此页！</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="last-updated"><span class="prefix">Last Updated: </span> <span class="time">2021-02-15 00:19:18</span></div></footer> <!----> <!----></main> <!----> <div class="comments-wrapper" data-v-0b87bb44><div class="valine-wrapper"><div id="valine"></div></div></div></div></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-44bd5a18 data-v-44bd5a18><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-44bd5a18><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-44bd5a18></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-44bd5a18></path></svg></div><!----><div data-v-48108b4c><div class="DetailsOpenFlag" style="right:1rem;bottom:9rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;font-size:14px;font-weight:500;display:none;" data-v-48108b4c>
 展开 

</div></div></div></div>
    <script src="/assets/js/app.55c4f7c3.js" defer></script><script src="/assets/js/5.86109dd2.js" defer></script><script src="/assets/js/1.74974061.js" defer></script><script src="/assets/js/2.ab8aa845.js" defer></script><script src="/assets/js/53.861fb703.js" defer></script><script src="/assets/js/18.cfe435f1.js" defer></script>
  </body>
</html>
